package com.otachn.jdbc;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.otachn.utiils.HeadPictureEntity;
import com.otachn.utiils.InformationEntity;
import com.otachn.utiils.PropertiesUtil;
import com.otachn.utiils.Role;

/**
 * 
 * @author ching
 *
 */
public class DBLink {
	private static DBTool dbTool=DBTool.newInstance();
	private static Connection conn=dbTool.getConnection();
	private static Logger logger = Logger.getLogger(PropertiesUtil.class);
	
	public static InformationEntity getInformationEntity (String sql){
		InformationEntity infomation = new InformationEntity();
		try{
			PreparedStatement pstmt=conn.prepareStatement(sql);
			
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()) {
				infomation.setId(rs.getInt("id"));
				infomation.setXm(rs.getString("xm"));
				infomation.setXb(rs.getString("xb"));
				infomation.setSfzh(rs.getString("sfzh"));
				infomation.setCsrq(rs.getString("csrq"));
				infomation.setZsbh(rs.getString("zsbh"));
				infomation.setZy(rs.getString("zy"));
				infomation.setBzrq(rs.getString("bzrq"));
				infomation.setLlcj(rs.getInt("llcj"));
				infomation.setCzcj(rs.getInt("czcj"));
				infomation.setPdcj(rs.getString("pdcj"));
				infomation.setPxdw(rs.getString("pxdw"));
				infomation.setFzdw(rs.getString("fzdw"));
			}
		}catch(SQLException e){
			logger.error("DBLink: " + e.getMessage());
		}
		return infomation;
	}
	
	public static Role getRoleEntity (String username, String password){
		Role role = new Role();
		String sql = "select * from role where username=? and password=?";
		try{
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				role.setId(rs.getInt("id"));
				role.setUsername(rs.getString("username"));
				role.setPassword(rs.getString("password"));
				role.setRole(rs.getString("role"));
			}
		}catch(SQLException e){
			logger.error("DBLink: " + e.getMessage());
		}
		return role;
	}
	
	public static HeadPictureEntity getHeadPictureEntity (String sql){
		HeadPictureEntity hp = new HeadPictureEntity();
		try{
			PreparedStatement pstmt=conn.prepareStatement(sql);
			
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()) {
				hp.setId(rs.getInt("id"));
				hp.setIid(rs.getInt("iid"));
				hp.setZp(rs.getBytes("zp"));
				hp.setSfzh(rs.getString("sfzh"));
//				hp.setZp(rs.getBinaryStream("xb"));
			}
		}catch(SQLException e){
			logger.error("DBLink: " + e.getMessage());
		}
		return hp;
	}
	
	/**
	 * created for {@link Update.java}
	 */
	public static List<String> getSfzhAndId (){
		String sql = "select * from information;";
		List<String> al = new ArrayList<String>();
		try{
			PreparedStatement pstmt=conn.prepareStatement(sql);
			
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				al.add(rs.getString("sfzh"));
			}
			
		}catch(SQLException e){
			logger.error("DBLink: " + e.getMessage());
		}
		return al;
	}
	
	public static void updateHeadPic(String sql, InputStream is){
		
		try{
			conn.setAutoCommit(false);
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setBinaryStream(1, is, is.available());
			
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try{
				conn.rollback();
			}catch(SQLException e1){
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	/**
	 * created for {@link Update.java}
	 * @param sfzh
	 * @param id
	 */
	public static void updateHeadPicById(String sfzh, int id) {
		String sql = "update headpicture set sfzh = ? where iid = ?";
		try{
			conn.setAutoCommit(false);
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, sfzh);
			pstmt.setInt(2, id);
			
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try{
				conn.rollback();
			}catch(SQLException e1){
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	public static boolean InsertIntoInformation(InformationEntity ie) {
		
		String sql = "insert into information(xm,xb, sfzh, csrq, zsbh, zy, bzrq, llcj, czcj, pdcj, pxdw, fzdw) " +
					"values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try{
			conn.setAutoCommit(false);
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, ie.getXm());
			pstmt.setString(2, ie.getXb());
			pstmt.setString(3, ie.getSfzh());
			pstmt.setString(4, ie.getCsrq());
			pstmt.setString(5, ie.getZsbh());
			pstmt.setString(6, ie.getZy());
			pstmt.setString(7, ie.getBzrq());
			pstmt.setInt(8, ie.getLlcj());
			pstmt.setInt(9, ie.getCzcj());
			pstmt.setString(10, ie.getPdcj());
			pstmt.setString(11, ie.getPxdw());
			pstmt.setString(12, ie.getFzdw());
			
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			return true;
		}catch(Exception e){
			try{
				conn.rollback();
			}catch(SQLException e1){
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return false;
	}
	
	
}
